package com.xpgk.mvc.statistic.dailyNursing;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import com.jfinal.kit.LogKit;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.render.Render;
import com.jfinal.render.RenderException;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class DailyNursingStatisticXLS extends Render{
	
	private static final String CONTENT_TYPE = "application/msexcel;charset=UTF-8";

	private final List<Record> data;
	private final String searchCriteria;
	private final String fileName;

	public DailyNursingStatisticXLS(List<Record> data,String searchCriteria, String fileName) {
		this.data = data;
		this.searchCriteria = searchCriteria;
		this.fileName = fileName;
	}
	
	
	@Override
	public void render() {
		this.response.reset();
		try {
			this.response.setHeader("Content-disposition",
					"attachment; filename*=UTF-8''" + URLEncoder.encode(this.fileName, "UTF-8") + ".xls");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		this.response.setContentType(CONTENT_TYPE);

		OutputStream os = null;
		try {
			os = this.response.getOutputStream();
			WritableWorkbook workbook = Workbook.createWorkbook(os);
			WritableSheet sheet = workbook.createSheet(this.fileName, 0);
			
			//主表头
			List<String> main_header = new ArrayList<>();
			main_header.add(fileName);
			for(int mhi = 0; mhi < 107; mhi++){
				main_header.add("");
			}
			
			//搜索表头
			List<String> search_header = new ArrayList<>();
			search_header.add("搜索条件：");
			search_header.add(searchCriteria);
			for(int mhi = 0; mhi < 106; mhi++){
				search_header.add("");
			}
			
			//注释表头
			List<String> ann_header = new ArrayList<>();
			ann_header.add("备注：");
			ann_header.add("白色字体代表使用产品");
			ann_header.add("");
			ann_header.add("红色代表涨称");
			ann_header.add("蓝色代表平称");
			ann_header.add("绿色代表掉称");
			for(int a=0;a<102;a++)
			{
				ann_header.add("");
			}
			
			//第一行表头
			List<String> one_header = new ArrayList<>();
			one_header.add("机构名称");
			one_header.add("姓名");
			one_header.add("手机");
			one_header.add("会员编号");
			one_header.add("进店日期");
			one_header.add("最初体重（斤）");
			one_header.add("标准体重（斤）");
			one_header.add("应减斤数（斤）");
			one_header.add("上月体重（斤）");
			for(int i = 1; i <32; i++){
				one_header.add(i+"");
				one_header.add(i+"isdc");
				one_header.add(i+"sycp");
			}
			one_header.add("当月到店次数");
			one_header.add("当月目标");
			one_header.add("当月实减斤数");
			one_header.add("当月目标达成率");
			one_header.add("是否达标");
			one_header.add("负责人");
			
			
			//列名
			List<String> columns = new ArrayList<>();
			columns.add("organname");
			columns.add("cusname");
			columns.add("cusphone");
			columns.add("cushybh");
			columns.add("intoStoreDate");
			columns.add("initialWeight");
			columns.add("stantardWeight");
			columns.add("weightLoss");
			columns.add("weightOfLastMonth");
			for(int n = 1; n < 32; n++){
				columns.add("day_"+n);
				columns.add("day_"+n+"_isdc");
				columns.add("day_"+n+"_sycp");
			}
			columns.add("arrivalNumberOfThisTime");
			columns.add("thisMonthAim");
			columns.add("thisMonthActualReduction");
			columns.add("yieldRateOfThisMonthAim");
			columns.add("ysstantard");
			columns.add("fzuser");

			int row = 0;//行数
			
			sheet.setColumnView(0, 15); // 设置列的宽度
			sheet.setColumnView(1, 15);
			sheet.setColumnView(2, 15); 
			sheet.setColumnView(3, 15);// 设置列的宽度
			sheet.setColumnView(4, 15);// 设置列的宽度
			sheet.setColumnView(5, 15);// 设置列的宽度
			sheet.setColumnView(6, 15);// 设置列的宽度
			sheet.setColumnView(7, 15);// 设置列的宽度
			sheet.setColumnView(8, 15); // 设置列的宽度
			sheet.setColumnView(71, 15);// 设置列的宽度
			sheet.setColumnView(72, 15);// 设置列的宽度
			sheet.setColumnView(73, 15);// 设置列的宽度
			sheet.setColumnView(74, 15);// 设置列的宽度
			sheet.setColumnView(75, 15);// 设置列的宽度
			for(int w=1;w<32;w++)
			{
				sheet.setColumnView(8+w*3, 0);
				sheet.setColumnView(8+w*3-1, 0);
			}
			sheet.setRowView(0, 600); // 设置行的高度
			sheet.setRowView(1, 600);
			sheet.setRowView(2, 600);
			sheet.setRowView(3, 600);
			//合并单元格
			sheet.mergeCells(0, 0,107, 0);// 列，行，列，行
			sheet.mergeCells(1, 1,107, 1);// 列，行，列，行
			sheet.mergeCells(1, 2,2, 2);// 列，行，列，行
			for(int s=0;s<8;s++)
			{
				sheet.mergeCells(0, data.size()-s+3, 8, data.size()-s+3);// 列，行，列，行
			}
			//主表头样式
			WritableFont mainwf = new WritableFont(WritableFont.TIMES, 18,  WritableFont.BOLD, true);
			WritableCellFormat wcfF = new WritableCellFormat(mainwf);
			wcfF.setBackground(Colour.GRAY_25);// 背景色
			wcfF.setAlignment(Alignment.CENTRE);// 水平对齐
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			wcfF.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			//搜索表头样式
			WritableCellFormat search_format = new WritableCellFormat();
			search_format.setBackground(Colour.GRAY_25);// 背景色
			search_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			search_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			//表头样式
			WritableCellFormat header_format = new WritableCellFormat();
			header_format.setBackground(Colour.GRAY_25);// 背景色
			header_format.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
//			header_format.setWrap(true);// 是否换行
			WritableFont font_wf = new WritableFont(WritableFont.TIMES,10,  WritableFont.BOLD, true,UnderlineStyle.NO_UNDERLINE,Colour.WHITE);
			WritableCellFormat header_format_font = new WritableCellFormat(font_wf);
			header_format_font.setBackground(Colour.GRAY_25);// 背景色
			header_format_font.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format_font.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			WritableFont red_font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
			WritableCellFormat header_format_red = new WritableCellFormat(red_font);
			header_format_red.setBackground(Colour.GRAY_25);// 背景色
			header_format_red.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format_red.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			WritableFont skyblue_font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.SKY_BLUE);
			WritableCellFormat header_format_skyblue = new WritableCellFormat(skyblue_font);
			header_format_skyblue.setBackground(Colour.GRAY_25);// 背景色
			header_format_skyblue.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format_skyblue.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			WritableFont green_font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.GREEN);
			WritableCellFormat header_format_green = new WritableCellFormat(green_font);
			header_format_green.setBackground(Colour.GRAY_25);// 背景色
			header_format_green.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format_green.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			//生成主表头
			for (int mi = 0; mi < main_header.size(); mi++) {
				String hea = main_header.get(mi);
				sheet.addCell(new Label(mi, row, hea, wcfF));
			}
			row++;
			//生成搜索表头
			for (int si = 0; si < search_header.size(); si++) {
				String hea = search_header.get(si);
				if(si==0)
				{
					sheet.addCell(new Label(si, row, hea, header_format));
				}else
				{
					sheet.addCell(new Label(si, row, hea, search_format));
				}
				
			}
			row++;
			//生成注释表头
			for (int an = 0; an < ann_header.size(); an++) {
				String header = ann_header.get(an);
				if(an==1)
				{
					sheet.addCell(new Label(an, row, header, header_format_font));
				}
				else if(an==3)
				{
					sheet.addCell(new Label(an, row, header, header_format_red));
				}else if(an==4)
				{
					sheet.addCell(new Label(an, row, header, header_format_skyblue));
				}else if(an==5)
				{
					sheet.addCell(new Label(an, row, header, header_format_green));
				}else
				{
					sheet.addCell(new Label(an, row, header, header_format));
				}
				
			}
			row++;
			//生成第一行表头
			for (int i = 0; i < one_header.size(); i++) {
				String header = one_header.get(i);
				sheet.addCell(new Label(i, row, header, header_format));
			}

			row++;
			

			//表格样式
			WritableCellFormat row_format = new WritableCellFormat();
			row_format.setAlignment(Alignment.CENTRE);// 水平对齐
			row_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			row_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			WritableCellFormat row_format1 = new WritableCellFormat();
			row_format1.setAlignment(Alignment.CENTRE);// 水平对齐
			row_format1.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			row_format1.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			row_format1.setBackground(Colour.GRAY_25);
			
			WritableFont wf = new WritableFont(WritableFont.TIMES, 10,  WritableFont.BOLD, true,UnderlineStyle.NO_UNDERLINE,Colour.WHITE);
			
			WritableCellFormat greenformat = new WritableCellFormat();
			greenformat.setAlignment(Alignment.CENTRE);// 水平对齐
			greenformat.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			greenformat.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			greenformat.setBackground(Colour.GREEN);
			WritableCellFormat greenformat1 = new WritableCellFormat(wf);
			greenformat1.setAlignment(Alignment.CENTRE);// 水平对齐
			greenformat1.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			greenformat1.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			greenformat1.setBackground(Colour.GREEN);
			
			WritableCellFormat redformat = new WritableCellFormat();
			redformat.setAlignment(Alignment.CENTRE);// 水平对齐
			redformat.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			redformat.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			redformat.setBackground(Colour.RED);
			WritableCellFormat redformat1 = new WritableCellFormat(wf);
			redformat1.setAlignment(Alignment.CENTRE);// 水平对齐
			redformat1.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			redformat1.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			redformat1.setBackground(Colour.RED);
			
			WritableCellFormat blueformat = new WritableCellFormat();
			blueformat.setAlignment(Alignment.CENTRE);// 水平对齐
			blueformat.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			blueformat.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			blueformat.setBackground(Colour.SKY_BLUE);
			WritableCellFormat blueformat1 = new WritableCellFormat(wf);
			blueformat1.setAlignment(Alignment.CENTRE);// 水平对齐
			blueformat1.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			blueformat1.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			blueformat1.setBackground(Colour.SKY_BLUE);
			//生成表
			for (int j = 0; j < data.size(); j++) {
				Record record = (Record) data.get(j);

				String[] values = getValues(columns, record);
				for(int i=0;i<9;i++)
				{	
						sheet.addCell(new Label(i, row, values[i], row_format));
				}
				for(int z=1;z<32;z++)
				{	
					 if(values[8+z*3-1]=="0")
					{	
						 
						if(values[8+z*3]!=null && values[8+z*3].equals("1"))
						{
							sheet.addCell(new jxl.write.Number(8+z*3-2, row, Double.parseDouble(values[8+z*3-2]), greenformat1));
						}
						else
						{
							sheet.addCell(new jxl.write.Number(8+z*3-2, row, Double.parseDouble(values[8+z*3-2]), greenformat));
						}
						sheet.addCell(new Label(8+z*3, row, values[8+z*3], row_format));
						sheet.addCell(new Label(8+z*3-1, row, values[8+z*3-1], row_format));
					}
					else if(values[8+z*3-1]=="1" )
					{	
						if(values[8+z*3]!=null && values[8+z*3].equals("1"))
						{
							sheet.addCell(new jxl.write.Number(8+z*3-2, row, Double.parseDouble(values[8+z*3-2]), blueformat1));
						}
						else
						{
							sheet.addCell(new jxl.write.Number(8+z*3-2, row, Double.parseDouble(values[8+z*3-2]), blueformat));
						}
						sheet.addCell(new Label(8+z*3, row, values[8+z*3], row_format));
						sheet.addCell(new Label(8+z*3-1, row, values[8+z*3-1], row_format));
					}
					else if(values[8+z*3-1]=="2" )
					{	
						if(values[8+z*3]!=null && values[8+z*3].equals("1"))
						{
							sheet.addCell(new jxl.write.Number(8+z*3-2, row, Double.parseDouble(values[8+z*3-2]), redformat1));
						}
						else
						{
							sheet.addCell(new jxl.write.Number(8+z*3-2, row, Double.parseDouble(values[8+z*3-2]), redformat));
						}
						sheet.addCell(new Label(8+z*3, row, values[8+z*3], row_format));
						sheet.addCell(new Label(8+z*3-1, row, values[8+z*3-1], row_format));
					}
					else
					{	
						sheet.addCell(new Label(8+z*3-2, row, values[8+z*3-2], row_format));
					}
				}
				for(int q=102;q<values.length;q++)
				{	
					if(q<74)
					{
						sheet.addCell(new jxl.write.Number(q, row, Double.parseDouble(values[q]), row_format));
					}else
					{
						sheet.addCell(new Label(q, row, values[q], row_format));
					}
				}
				row++;
			}

			workbook.write();
			workbook.close();
		} catch (IOException e) {
			throw new RenderException(e);
		} catch (WriteException e) {
			e.printStackTrace();
		} finally {
			try {
				if (os != null) {
					os.flush();
					os.close();
				}
			} catch (IOException e) {
				LogKit.error(e.getMessage(), e);
			}
		}
	}

	private static String[] getValues(List<String> columns, Record record) {
		String[] values = new String[columns.size()];
		int i = -1;
		for (String col : columns) {
			i++;
			Object value = record.get(col);
			if (value == null) {
				continue;
			}
			values[i] = value.toString();
		}
		return values;
	}


}
